package cn.agiledata.bank.common.dao;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.lob.SerializableBlob;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

import uk.ltd.getahead.dwr.WebContext;
import uk.ltd.getahead.dwr.WebContextFactory;
import cn.agiledata.bank.account.dao.IAccountDAO;
import cn.agiledata.bank.account.service.AccountSummary;
import cn.agiledata.bank.common.util.Constant;
import cn.agiledata.bank.common.util.DateUtil;
import cn.agiledata.bank.common.util.OperateUtil;
import cn.agiledata.bank.common.util.PageBean;
import cn.agiledata.bank.common.util.SecurityUtil;
import cn.agiledata.bank.common.util.StringUtil;
import cn.agiledata.bank.common.util.WeatherUtil;
import cn.agiledata.bank.hibernateBean.Account;
import cn.agiledata.bank.hibernateBean.AccountBalance;
import cn.agiledata.bank.hibernateBean.AccountBank;
import cn.agiledata.bank.hibernateBean.AccountHis;
import cn.agiledata.bank.hibernateBean.AccountSetup;
import cn.agiledata.bank.hibernateBean.AccountSetupD;
import cn.agiledata.bank.hibernateBean.ListAccount;
import cn.agiledata.bank.hibernateBean.MoneyRole;
import cn.agiledata.bank.hibernateBean.Operator;
import cn.agiledata.bank.hibernateBean.PeerInnerAccount;
import cn.agiledata.bank.hibernateBean.SubAccount;
import cn.agiledata.bank.hibernateBean.Sysbook;
import cn.agiledata.bank.hibernateBean.SystemLog;
import cn.agiledata.bank.hibernateBean.VnvAccount;
import cn.agiledata.bank.myspace.bean.AccountHisQueryDetailBean;
import cn.agiledata.bank.myspace.bean.PaperBean;
import cn.agiledata.bank.myspace.service.AuthenticationStatus;

import com.vnv.infrastructure.email.service.IMailService;
import com.vnv.infrastructure.email.service.VNVMail;
import com.vnv.infrastructure.logic.IPBAppFactory;
import com.vnv.infrastructure.logic.IPBHibernate3BaseMgr;

public class AccountTemplate extends IPBHibernate3BaseMgr implements
		AccountTemplateInter {
	/**
	 * 输入参数：subAccountId：需要判断的子帐户ｉｄ moduleType： 操作类型 返回值：ｔｒｕｅ：需要电话通知 ｆａｌｓｅ：不需要
	 * 返回getAccountListPageBeanBussiness值：0：不打电话 1：自己打电话 3：打给双方
	 */
	private String email;
	private IMailService notificationService;
	private IAccountDAO accountDAO;

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public IMailService getNotificationService() {
		return notificationService;
	}

	public void setNotificationService(IMailService notificationService) {
		this.notificationService = notificationService;
	}

	public int ifCanCall(String subAccountId, String moduleType) {
		SubAccount sub = this.getSubAccount(subAccountId);
		AccountSetup as = null;
		List list = getHibernateTemplate()
				.find("from AccountSetup s where s.account.id=? and s.setId=? order by s.id",
						new Object[] { new Long(subAccountId),
								Constant.ACCOUNT_SETUP_TYPE_A });
		if (list.size() != 0) {
			as = (AccountSetup) list.get(0);
			Iterator iter = as.getSetupds().iterator();
			while (iter.hasNext()) {
				AccountSetupD asd = (AccountSetupD) iter.next();
				if (asd.getActionId().equals(moduleType)) {
					return Integer.parseInt(asd.getObjectFlag());
				}
			}
		}
		return 0;
	}

	public boolean ifHasBank(String bankId) {
		List vnvAccountList = this.getHibernateTemplate().find(
				" from VnvAccount t where t.defaultFlag = 'Y' "
						+ "and t.accountType='N' and t.status='Y'");
		for (int i = 0; i < vnvAccountList.size(); i++) {
			VnvAccount vnv = (VnvAccount) vnvAccountList.get(i);
			if (vnv.getBankInfo().getId().toString().equals(bankId))
				return true;
		}
		return false;
	}

	public List getSetup(String subAccountId) {
		// TODO Auto-generated method stub
		List resultList = getHibernateTemplate().find(
				"from AccountSetup s where s.account.id=? order by s.id",
				new Object[] { new Long(subAccountId) });
		return resultList;
	}

	public List getTran(String subAccountId) {
		List resultList = getHibernateTemplate()
				.find("from Transaction t where (t.debit.id=? or t.credit.id=?) and"
						+ " (t.transStatus<>'3' and t.transStatus<>'4' and t.transStatus<>'6'"
						+ " and t.transStatus<>'9')",
						new Object[] { new Long(subAccountId),
								new Long(subAccountId) });
		return resultList;
	}

	public boolean saveOrUpdateSetup(List list) {
		// TODO Auto-generated method stub
		List resultList = getSetup(((AccountSetup) list.get(0)).getAccount()
				.getId().toString());
		if (resultList.size() > 0) {
			this.getHibernateTemplate().deleteAll(resultList);
		}
		for (int i = 0; i < list.size(); i++) {
			AccountSetup as = (AccountSetup) list.get(i);
			if (as.getSetupds().size() > 0) {
				saveObj(as);
			}
		}
		return false;
	}

	public boolean ifAccountIsEffect(double checkAccount) {
		// TODO Auto-generated method stub
		return checkAccount > 0 ? true : false;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#compareAccountMoney(java
	 * .lang.String, double)
	 */
	public int compareAccountMoney(String SubAccountId, Double wantedCount) {
		double nowCount = this.getSubAccount(SubAccountId).getAccountBalance()
				.getBalance().doubleValue();
		if (wantedCount == null || wantedCount.doubleValue() <= 0)
			return 1;
		return (nowCount - wantedCount.doubleValue()) >= 0 ? 0 : 2;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#compareAccountMoney(java
	 * .lang.String, double)
	 */
	public String accountMoney(String SubAccountId) {
		return OperateUtil.parseDec(this.getSubAccount(SubAccountId)
				.getAccountBalance().getBalance(), "##,###,##0.00");
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#compareAccountPickUpPassword
	 * (cn.agiledata.bank.hibernateBean.Account, java.lang.String)
	 */
	public boolean compareAccountPickUpPassword(Account account,
			String wantedPassword) {
		String payPassword = account.getPayPassword();
		return (payPassword.equals(SecurityUtil
				.encryptoMD5String(wantedPassword))) ? true : false;
	}

	public boolean corCompareAccountPickUpPassword(Operator operator,
			String wantedPassword) {
		String payPassword = operator.getPayPassword();
		return (payPassword.equals(SecurityUtil
				.encryptoMD5String(wantedPassword))) ? true : false;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#getAccountListPageBeanBussiness
	 * (java.lang.String, int, int)
	 */
	public PageBean getAccountListPageBeanBussiness(List id, int startPage,
			int pageSize) {

		List statusList = new ArrayList();
		statusList.add(Constant.TRANS_STATUS_INIT);
		statusList.add(Constant.TRANS_STATUS_WAITFORBUYER);
		statusList.add(Constant.TRANS_STATUS_WAITFORBUYERAFFIRM);
		// List ids = new ArrayList();
		// ids.add(id);
		Object[] para = { id, statusList };
		return getAccountListPageBean(para, startPage, pageSize,
				Constant.ACTION_TYPE_C);
	}

	public SubAccount getSubAccount(String id) {
		String sqlStr = "from SubAccount where id=?";
		Object o = getObject(id, sqlStr);
		return (SubAccount) o;
	}

	public SubAccount getSubAccountBySubAccountId(String subAccountId) {
		String hql = "from SubAccount sa where sa.accountId=?";
		List resultList = getHibernateTemplate().find(hql, subAccountId);
		return resultList.size() == 0 ? null : (SubAccount) resultList.get(0);
	}

	private Object getObject(String id, String sqlStr) {
		Object o = null;

		if (StringUtils.isNotEmpty(id)) {
			List resultList = getHibernateTemplate().find(sqlStr,
					new Object[] { new Long(id) });
			if (resultList != null && resultList.size() > 0) {
				o = resultList.get(0);
			}
		}
		if (o != null)
			return (SubAccount) o;
		return null;
	}

	public SubAccount getSubAccountByAccountId(String id) {
		String sqlStr = "from SubAccount where account.id=?";
		Object o = getObject(id, sqlStr);
		return (SubAccount) o;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#getAccountListPageBeanDownSite
	 * (java.lang.Object[], int, int, java.lang.String)
	 */
	public PageBean getAccountListPageBeanDownSite(Object[] para,
			int startPage, int pageSize, String actionType) {
		if (para[1] == null || para[1].equals("-1")) {
			List statusList = new ArrayList();
			statusList.add(Constant.TRANS_STATUS_INIT);
			statusList.add(Constant.TRANS_STATUS_TRANSACTIONSUCCESS);
			statusList.add(Constant.TRANS_STATUS_TRANSACTIONCLOSE);
			para[1] = statusList;
		}
		if (para[2] == null) {
			para[2] = DateUtil.string2Date("1900-01-01");
		} else {
			para[2] = DateUtil.string2Date((String) para[2]);
		}
		if (para[3] == null) {
			para[3] = new Date();
		} else {
			para[3] = DateUtil.string2Date((String) para[3]);
		}
		return getAccountListPageBean(para, startPage, pageSize,
				Constant.ACTION_TYPE_B);
	}

	private PageBean getAccountListPageBean(Object[] para, int startPage,
			int pageSize, String actionType) {

		PageBean pb = new PageBean(getListAmountCount(para, actionType),
				startPage, pageSize);
		pb.setData(getListAccountByUserId(para, startPage, pageSize, actionType));
		return pb;
	}

	private List getListAccountByUserId(final Object[] para,
			final int startPage, final int pageSize, final String actionType) {
		List resultList = new ArrayList();

		resultList = (List) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException {
						StringBuffer hiSql = new StringBuffer(
								"select t.trans_id as {ListAccount.tranId}"
										+ ",t.status as {ListAccount.status}"
										+ ",to_char(t.begin_date,'yyyy-MM-dd') as {ListAccount.beginDate}"
										+ ",t.amount as {ListAccount.amount}"
										+ ",t.trade as {ListAccount.trade}"
										+ ",t.id as {ListAccount.id}"
										+ ",acc.name as {ListAccount.name}"
										+ ",sub_1.account_name as {ListAccount.selfName}"
										+ ",t.comeorgo as {ListAccount.comeOrGo}"
										+ ",t.trans_status  as {ListAccount.tranStatus}"
										+ ",null  as {ListAccount.relating}"
										+ ",null  as {ListAccount.remark}"
										+ ",t.id as {ListAccount.accid}"
										+ ",null  as {ListAccount.nameid}"
										+ ",null  as {ListAccount.selfid}"
										+ " from ( "
										+ "		select t.RELATING_SEQ,t.trans_id,s.list_name as status,t.begin_date,t.amount,s1.list_name as trade,t.id, "
										+ "		case when t.debit_seq in (:id) then t.credit_seq "
										+ "		when t.credit_seq in (:id) then t.debit_seq end as offsize "
										+ "		,case when t.debit_seq in (:id) then '0' "
										+ // 付款方
										"		when t.credit_seq in (:id) then '1' end as comeorgo,t.trans_status, "
										+ "		case when t.debit_seq in (:id) then t.debit_seq "
										+ "		when t.credit_seq in (:id) then t.credit_seq end as selfsize "
										+ "		from transaction t   "
										+ // 收款方

										"		left join sysbook s on "
										+ "			s.type_id='TRANS_STATUS' and t.trans_status = s.list_id  "
										+ "		left join sysbook s1 on "
										+ "			s1.type_id='TRADE_TYPE' and t.trade_type= s1.list_id "
										+

										"		where t.action_seq = :actiontype  "
										+ "		and t.trans_status in (:status)  ");
						if (actionType.equals(Constant.ACTION_TYPE_B)) {
							hiSql.append("			and t.begin_date>:startdate and t.begin_date <:enddate ");
						}

						hiSql.append("			and (t.debit_seq in (:id) or t.credit_seq in (:id)) "
								+ "		) t "
								+ "		left join sub_account sub_1 on "
								+ "			sub_1.id = t.selfsize  "
								+ "			,account acc,sub_account sub "
								+ "		where sub.main_account_seq = acc.id and sub.id = t.offsize  "
								+ "		order by t.begin_date desc");

						Query query = session.createSQLQuery(hiSql.toString())
								.addEntity("ListAccount", ListAccount.class)
								.setFirstResult((startPage - 1) * pageSize)
								.setParameterList("id", (List) para[0])
								.setParameter("actiontype", actionType)
								.setParameterList("status", (List) para[1])
								.setMaxResults(pageSize);
						if (actionType.equals(Constant.ACTION_TYPE_B)) {
							query.setParameter("startdate", para[2])
									.setParameter("enddate", para[3]);
						}
						return query.list();
					}
				}, true);

		return resultList;
	}

	private int getListAmountCount(final Object[] para, final String actionType) {
		Long count = (Long) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException {
						StringBuffer hiSql = new StringBuffer(
								"select count(*) as count from transaction t "
										+ "where t.action_seq = :actiontype and t.trans_status in (:status) "
										+ "and (t.debit_seq in ( :id) or t.credit_seq in (:id))");
						if (actionType.equals(Constant.ACTION_TYPE_B)) {
							hiSql.append("			and t.begin_date>:startdate and t.begin_date <:enddate ");
						}
						Query query = session.createSQLQuery(hiSql.toString())
								.addScalar("count", Hibernate.LONG)
								.setParameterList("id", (List) para[0])
								.setParameter("actiontype", actionType)
								.setParameterList("status", (List) para[1]);
						if (actionType.equals(Constant.ACTION_TYPE_B)) {
							query.setParameter("startdate", para[2])
									.setParameter("enddate", para[3]);
						}
						return query.uniqueResult();
					}
				}, true);
		return count.intValue();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see cn.agiledata.bank.common.dao.AccountTemplateInter#getInsideAccount()
	 */
	public List getInsideAccount() {
		// modify by zln 2007-10-23 修改为按照accountid数字顺序排序，
		// 由于外部使用时为按照list位置取值且accountid为varchar类型，导致大于9的accountid排序错误
		// 此修改方法为临时使用，accountid数据库为varchar类型，存在风险。尽快修正
		List resultList = getHibernateTemplate()
				.find("from SubAccount s where s.account.accountType=? order by to_number(s.accountId)",
						new Object[] { Constant.ACCOUNT_TYPE_INITIAL });
		return resultList;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see cn.agiledata.bank.common.dao.AccountTemplateInter#getRemitAccount()
	 */
	public SubAccount getRemitAccount() {
		return this.getSubAccount("5");
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see cn.agiledata.bank.common.dao.AccountTemplateInter#getBankSubAccount()
	 */
	public SubAccount getBankSubAccount() {
		return this.getSubAccount("1");
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see cn.agiledata.bank.common.dao.AccountTemplateInter#getDownSiteSubAccount()
	 */
	public SubAccount getDownSiteSubAccount() {
		return this.getSubAccount("6");
	}

	public SubAccount getCardSubAccount() {
		return this.getSubAccount("9");
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see cn.agiledata.bank.common.dao.AccountTemplateInter#getDownSiteSubAccount()
	 */
	public SubAccount getInterestSubAccount() {
		return this.getSubAccount("8");
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#getSubAccountByAccount(java
	 * .lang.String)
	 */
	public SubAccount getSubAccountByAccount(String id) {
		Account debit = (Account) getHibernateTemplate().find(
				"from Account where id=?", new Object[] { new Long(id) })
				.get(0);
		Iterator SubDebits = debit.getSubAccounts().iterator();
		SubAccount subDebit = (SubAccount) SubDebits.next();
		return subDebit;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#getAccount(java.lang.String)
	 */
	public Account getAccountByName(String accountName) {
		List result = getHibernateTemplate()
				.find("from Account a where lower(a.email)=? or a.mobile=? or a.accountId=?",
						new Object[] { accountName.toLowerCase(), accountName,
								accountName });
		if (result != null && result.size() > 0) {
			return (Account) result.get(0);
		}
		return null;
	}

	public String getAccountNameByName(String accountName) {
		if (StringUtil.isStrEmpty(accountName))
			return "";
		Account account = getAccountByName(accountName);
		// return WeatherUtil.getNameOrEmailOrMobileByAccount(account);
		if (account.getAccountType().equals(Constant.ACCOUNT_TYPE_INITIAL)) {
			return "";
		}
		return StringUtil.getNotNullValue(account.getName());
	}

	/**
	 * 根据帐号ID取得帐户
	 * 
	 * @param accountID
	 * @return
	 */
	/*
	 * public Account getAccountByAccountID(String accountID){ List result =
	 * getHibernateTemplate().find("from Account a where a.account_id=? ",new
	 * Object[]{accountID}); if (result != null && result.size()>0){ return
	 * (Account)result.get(0); } return null; }
	 */

	public Account getAccount(String id) {

		Object o = null;
		if (id != null)
			o = getHibernateTemplate().get(Account.class, new Long(id));
		if (o != null)
			return (Account) o;
		return null;
	}

	public boolean ifHasAccountInPeer(String peerAcctSeq, String acctSeq) {
		boolean result = false;
		PeerInnerAccount obj = getPeerAcctHis(peerAcctSeq, acctSeq);
		if (obj != null) {
			result = true;
		}
		return result;
	}

	public PeerInnerAccount getPeerAcctHis(String peerAcctSeq, String acctSeq) {
		List resultList = getHibernateTemplate()
				.find("from PeerInnerAccount where peerAccount.id = ? and account.id=?",
						new Object[] { new Long(peerAcctSeq), new Long(acctSeq) });
		if (resultList != null && resultList.size() > 0) {
			return (PeerInnerAccount) resultList.get(0);
		}
		return null;
	}

	public PeerInnerAccount getPeerAcctHis(String peerAcctSeq, String acctSeq,
			String type) {
		List resultList = getHibernateTemplate()
				.find("from PeerInnerAccount where peerAccount.id = ? and account.id=? and source=?",
						new Object[] { new Long(peerAcctSeq),
								new Long(acctSeq), type });
		if (resultList != null && resultList.size() > 0) {
			return (PeerInnerAccount) resultList.get(0);
		}
		return null;
	}

	public void saveAccountInPeer(PeerInnerAccount pah) {
		getHibernateTemplate().saveOrUpdate(pah);
	}

	public void saveObj(Object obj) {
		getHibernateTemplate().saveOrUpdate(obj);
	}

	/**
	 * 保存账户信息（photoStream是个人照片，cardPhotoStream是身份证背面图片，
	 * cardPhotoBackStream是身份证背面图片） 正面和背面图片均不空时为上传二代证图片； 正面图片为空时为上传一代证图片；
	 * 正面和背面图片均为空时为传真身份证复印件。
	 */
	public void saveOrUpdateAccount(final Account acc,
			final InputStream photoStream, final InputStream cardPhotoStream,
			final InputStream cardPhotoBackStream) throws Exception {

		// org.hibernate.Transaction tx = session.beginTransaction();

		try {
			TransactionTemplate springTransactionTemplate = new TransactionTemplate(
					(PlatformTransactionManager) IPBAppFactory
							.getObj("transactionManager"));
			springTransactionTemplate.execute(new TransactionCallback() {
				public Object doInTransaction(TransactionStatus status) {
					Session session = getHibernateTemplate()
							.getSessionFactory().getCurrentSession();
					if (photoStream != null) {// 如果是上传照片，先清掉原照片。
						acc.setPhoto(Hibernate.createBlob(new byte[1]));
					}
					if (cardPhotoStream != null) {// 如果是上传身份证图片，先清掉原身份证图片（不管本次上传的是一代证还是二代证，正面图片和背面图片都清掉）
						acc.setTempCardPhoto(Hibernate.createBlob(new byte[1]));
						acc.setTempCardPhotoBack(Hibernate
								.createBlob(new byte[1]));
					}

					// WeatherUtil.fillCreateAndUpdateInfo(acc,
					// acc.getId().toString());
					WeatherUtil.fillUpdateInfo(acc, acc.getId().toString());
					session.saveOrUpdate(acc);

					session.flush();
					session.refresh(acc, LockMode.UPGRADE);
					if (photoStream != null) {
						SerializableBlob sb = (SerializableBlob) acc.getPhoto();
						Blob blob = sb.getWrappedBlob();
						try {
							readPictrueToBlob(blob, photoStream);
						} catch (Exception e) {
							e.printStackTrace();
							status.setRollbackOnly();
						}
						acc.setPhoto(blob);
					}

					if (cardPhotoStream != null) {
						SerializableBlob cardSb = (SerializableBlob) acc
								.getTempCardPhoto();
						Blob cardBlob = cardSb.getWrappedBlob();
						try {
							readPictrueToBlob(cardBlob, cardPhotoStream);
						} catch (Exception e) {
							e.printStackTrace();
							status.setRollbackOnly();
						}
						acc.setTempCardPhoto(cardBlob);
					}
					if (cardPhotoBackStream != null) {
						SerializableBlob cardSbBack = (SerializableBlob) acc
								.getTempCardPhotoBack();
						Blob cardBlobBack = cardSbBack.getWrappedBlob();
						try {
							readPictrueToBlob(cardBlobBack, cardPhotoBackStream);
						} catch (Exception e) {
							e.printStackTrace();
							status.setRollbackOnly();
						}
						acc.setTempCardPhotoBack(cardBlobBack);
					}
					session.saveOrUpdate(acc);
					return null;
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
		}

		// tx.commit();
	}

	// private void readPictrueToBlob(Blob blob, InputStream
	// fileStream)
	// throws SQLException, FileNotFoundException, IOException {
	// OutputStream img = blob.getBinaryOutputStream();
	//
	// // FileInputStream imput = new FileInputStream(fileStr);
	// InputStream imput = (InputStream) fileStream;
	//
	// byte[] buf = new byte[524288];
	// int len;
	// while ((len = imput.read(buf)) > 0) {
	// img.write(buf, 0, len);
	// }
	// img.close();
	// imput.close();
	// }

	private void readPictrueToBlob(Blob blob, InputStream in)
			throws SQLException, IOException {
		OutputStream out = blob.setBinaryStream(1);
		byte[] bytes = new byte[1024];
		int len = 0;
		while ((len = in.read(bytes)) > 0) {
			out.write(bytes, 0, len);
		}
		out.close();
		in.close();
	}

	/**
	 * acc 需要保存的account对象 photoPath 图片路径 cardPhotoPath 卡图片路径
	 * 只在cn.agiledata.bank.test.AccountTest.java里的ttestUpdateAccount()中使用
	 */
	public void saveOrUpdateAccount(final Account acc,
			final String photoStream, final String cardPhotoStream)
			throws Exception {

		// org.hibernate.Transaction tx = session.beginTransaction();

		TransactionTemplate springTransactionTemplate = new TransactionTemplate(
				(PlatformTransactionManager) IPBAppFactory
						.getObj("transactionManager"));
		springTransactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus status) {
				Session session = getHibernateTemplate().getSessionFactory()
						.getCurrentSession();
				acc.setPhoto(Hibernate.createBlob(new byte[1]));
				acc.setCardPhoto(Hibernate.createBlob(new byte[1]));
				// WeatherUtil.fillCreateAndUpdateInfo(acc, "1111");
				WeatherUtil.fillUpdateInfo(acc, "1111");
				session.saveOrUpdate(acc);

				session.flush();
				session.refresh(acc, LockMode.UPGRADE);

				SerializableBlob sb = (SerializableBlob) acc.getPhoto();

				Blob blob = (Blob) sb.getWrappedBlob();
				try {
					readPictrueToBlob(blob, photoStream);
				} catch (Exception e) {
					status.setRollbackOnly();
				}
				acc.setPhoto(blob);

				SerializableBlob cardSb = (SerializableBlob) acc.getCardPhoto();
				Blob cardBlob = (Blob) cardSb.getWrappedBlob();
				try {
					readPictrueToBlob(cardBlob, cardPhotoStream);
				} catch (Exception e) {
					status.setRollbackOnly();
				}

				acc.setCardPhoto(cardBlob);
				session.saveOrUpdate(acc);

				return null;
			}
		});

		// tx.commit();
	}

	private void readPictrueToBlob(Blob blob, String fileStr)
			throws SQLException, FileNotFoundException, IOException {
		OutputStream img = blob.setBinaryStream(0);

		FileInputStream in = new FileInputStream(fileStr);

		byte[] buf = new byte[1024];
		int len;
		while ((len = in.read(buf)) > 0) {
			img.write(buf, 0, len);
		}
		img.close();
		in.close();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#updateAccount(cn.agiledata.bank
	 * .hibernateBean.Account)
	 */
	public boolean updateAccount(Account account) {
		getHibernateTemplate().update(account);
		return true;
	}

	/*
	 * 保存账户历史 (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#updateAccount(cn.agiledata.bank
	 * .hibernateBean.Account)
	 */
	public boolean saveHisAccount(AccountHis accountHis) {
		getHibernateTemplate().save(accountHis);
		return true;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#getAccountBank(java.lang
	 * .String)
	 */
	public AccountBank getAccountBank(String l) {
		if (l == null)
			return null;
		return (AccountBank) getHibernateTemplate().get(AccountBank.class,
				new Long(l));
	}

	public List getAccountBankListByAccountIdAndBankName(String AccountId,
			String bankName) {
		String hSql = " from AccountBank where accountId = ? and bankInfo.id = ? and isValid = 'Y' ";
		return this.getHibernateTemplate().find(hSql,
				new Object[] { AccountId, bankName });
	}

	/**
	 * 根据银行卡号码和账户ID检查是否唯一
	 * 
	 * @param accountID
	 *            银行卡号码
	 * @param accountSeq
	 *            账户ID
	 * @return boolean
	 */
	public boolean isUniqueAccountBank(String accountId, Long accountSeq,
			Long id) throws HibernateException {
		String queryString = "select count(id) as row_num from account_bank where account_id=? and account_seq=? and isValid='Y'";
		if (id != null) {
			queryString += " and id <> ?";
		}
		SQLQuery queryObj = this.getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createSQLQuery(queryString);
		queryObj.setParameter(0, accountId);
		queryObj.setParameter(1, accountSeq);
		if (id != null) {
			queryObj.setParameter(2, id);
		}

		Long rowNum = (Long) queryObj.addScalar("row_num", Hibernate.LONG)
				.uniqueResult();

		if (rowNum.intValue() == 0)
			return true;
		else
			return false;
	}

	/**
	 * 根据银行卡号码和账户ID检查是否唯一
	 * 
	 * @param accountID
	 *            银行卡号码
	 * @param accountSeq
	 *            账户ID
	 * @return boolean
	 */
	public boolean isUniqueAccountAlias(String bankAlias, Long accountSeq,
			Long id) throws HibernateException {
		String queryString = "select count(id) as row_num from account_bank where bank_alias=? and account_seq=? and isValid='Y'";
		if (id != null) {
			queryString += " and id <> ?";
		}
		SQLQuery queryObj = this.getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createSQLQuery(queryString);
		queryObj.setParameter(0, bankAlias);
		queryObj.setParameter(1, accountSeq);
		if (id != null) {
			queryObj.setParameter(2, id);
		}
		Long rowNum = (Long) queryObj.addScalar("row_num", Hibernate.LONG)
				.uniqueResult();

		if (rowNum.intValue() == 0)
			return true;
		else
			return false;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#getAccountHisQueryDetail
	 * (java.lang.String, java.lang.String)
	 */
	public AccountHisQueryDetailBean getAccountHisQueryDetail(
			String TransactionID, String accountID) {
		String hSql = "";
		String status = "login";
		if (StringUtil.isStrEmpty(accountID)) {
			status = "nologin";
		}
		// REMARK : MODIFY BY WUBO 20080916
		if (status.equals("login")) {
			hSql = " select new cn.agiledata.bank.myspace.bean.AccountHisQueryDetailBean(a, t) from "
					+ " Transaction t ,Account a "
					+ "where t.id=:TransactionID and (t.credit.account.id=a.id or t.debit.account.id=a.id) and a.id=:accountID ";
		} else {
			hSql = " select new cn.agiledata.bank.myspace.bean.AccountHisQueryDetailBean(a, t) from "
					+ " Transaction t ,Account a "
					+ "where t.transId=:TransactionID and a.id=t.credit.account.id ";
		}

		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(hSql);
		if (status.equals("login")) {
			q.setParameter("TransactionID", new Long(TransactionID));
		} else {
			q.setParameter("TransactionID", TransactionID);
		}
		if (status.equals("login")) {
			q.setParameter("accountID", new Long(accountID));
		}
		List list = q.list();
		if (list != null && list.size() > 0)
			return (AccountHisQueryDetailBean) list.get(0);
		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#getAccountHisQueryDetail
	 * (java.lang.String, java.lang.String)
	 */
	public PaperBean getPaper(String TransactionID, String accountID) {
		String hSql = "";
		String status = "login";
		if (StringUtil.isStrEmpty(accountID)) {
			status = "nologin";
		}
		if (status.equals("login")) {
			hSql = " select new cn.agiledata.bank.myspace.bean.PaperBean(a, t) from "
					+ " Transaction t ,Account a "
					+ "where t.id=:TransactionID and a.id=:accountID ";
		} else {
			hSql = " select new cn.agiledata.bank.myspace.bean.PaperBean(a, t) from "
					+ " Transaction t ,Account a "
					+ "where t.transId=:TransactionID and a.id=t.credit.account.id ";
		}

		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(hSql);
		if (status.equals("login")) {
			q.setParameter("TransactionID", new Long(TransactionID));
		} else {
			q.setParameter("TransactionID", TransactionID);
		}
		if (status.equals("login")) {
			q.setParameter("accountID", new Long(accountID));
		}
		List list = q.list();
		if (list != null)
			return (PaperBean) list.get(0);
		return null;
	}

	/**
	 * 得到账户历史分页列表
	 * 
	 * @param param
	 * @param pageNo
	 * @param recordPerPage
	 * @return
	 */
	public PageBean getAccountHisListPageBean(Map param, int pageNo,
			int recordPerPage) {
		PageBean pb = new PageBean(getAccountHisListAmountCount(param), pageNo,
				recordPerPage);
		pb.setData(getAccountHisQueryList(param, pageNo, recordPerPage));
		return pb;
	}

	/**
	 * 
	 * @param map
	 * @return
	 */
	private int getAccountHisListAmountCount(Map param) {
		// 交易对方
		String id = getParam(param, "accountID"); // 此值必须有

		String name = getParam(param, "accountName");

		String subAccountID = getParam(param, "subAccountID");

		String actionTypeID = getParam(param, "actionTypeID");

		// 交易对方

		String otherAccountName = getParam(param, "otherAccountID");

		// 交易号
		String transId = getParam(param, "transId");
		// 是否显示订单编号
		boolean showOrderId = (Boolean) param.get("showOrderId") == null ? false
				: ((Boolean) param.get("showOrderId")).booleanValue();

		// 订单编号
		String orderId = getParam(param, "orderId");
		// 显示收款付款
		String amountView = getParam(param, "amountView");// 0收1付
		// 交易金额范围
		String amountMin = getParam(param, "amountMin");
		String amountMax = getParam(param, "amountMax");

		String lastDate = getParam(param, "lastDate"); // 查询范围
		String beginDate = getParam(param, "beginDate");// 开始时间

		String endDate = getParam(param, "endDate"); // 结束时间

		// String dateType = getParam(param, "dateType");// 类型切换

		String transStatusID = getParam(param, "transStatusID");// 交易状态

		// edited by 吴波 20080327网关商户 结算 交易明细查询
		String gateWayBeginDate = getParam(param, "gateWayBeginDate");// 开始时间
		String gateWayEndDate = getParam(param, "gateWayEndDate"); // 结束时间
		List typeList = (List) param.get("typeList");
		String returnTrans = getParam(param, "returnTrans");
		String transStatus = getParam(param, "transStatus");

		Date nowDate = new Date();
		Date lastDateValue = new Date();

		boolean isNullId = isNull(id);
		boolean isNullSubAcountID = isNull(subAccountID);
		boolean isNullactionTypeID = isNull(actionTypeID);
		boolean isNullotherAccount = isNull(otherAccountName);
		boolean isNullamountView = isNull(amountView);
		boolean isNullTransId = isNull(transId);
		boolean isNullOrderId = isNull(orderId) || !showOrderId;
		boolean isNullamount = isNull(amountMin) && isNull(amountMax);
		// 自定义
		// boolean isNullDate = dateType == null || !dateType.equals("1")
		// || isNull(endDate) || isNull(beginDate);
		boolean isNullDate = isNull(endDate) && isNull(beginDate);
		// 查询范围
		boolean isNulllastDate = !(!isNull(lastDate) && !lastDate.equals("0"));

		boolean isNulltransStatusID = isNull(transStatusID);

		String hSql = " select count(*) as count from "
				+ " SubAccount s, Transaction t "
				+ "where (t.credit.id=s.id or t.debit.id=s.id)";

		if (!isNullId)
			hSql += " and s.account.id=:id";
		// 子账户
		if (!isNullSubAcountID) {
			hSql += " and s.id=:subAccountID ";
		}

		hSql += " and not exists(select 1 from SubAccount sa, SubAccount sb "
				+ "where sa.account.id=:id and sb.account.id=:id and sa.id>sb.id and sa.id=s.id";

		if (!isNullSubAcountID) {
			hSql += " and sb.id=:subAccountID ";
		}
		hSql += " and sa.id=t.credit.id and sb.id=t.debit.id) and exists (select 1 from "
				+ "SubAccount sa, Account a, SubAccount sb where ((t.credit.id=sa.id and t.debit.id=sb.id)"
				+ "or (t.debit.id=sa.id and t.credit.id=sb.id)) and sb.account.id=a.id and sa.id<>sb.id";
		// 交易对方
		if (!isNullotherAccount)
			hSql += " and a.name like :debitID ";

		hSql += " and ((a.id<>s.account.id) or (a.id=s.account.id and sa.account.id=sb.account.id))) ";
		// 交易类型
		if (!isNullactionTypeID)
			hSql += " and t.actionType.id=:actionTypeID";

		// 交易号
		if (!isNullTransId)
			hSql += " and t.transId like :transId ";

		// 订单编号
		if (!isNullOrderId)
			hSql += " and t.orderId like :orderId ";

		// 显示收付款
		if (!isNullamountView) {
			// 收
			if (amountView.equals("1"))
				hSql += " and t.credit.id=s.id ";
			// 付
			else if (amountView.equals("0"))
				hSql += " and t.debit.id=s.id ";
		}

		// 交易金额
		if (!isNullamount)
			if (!isNull(amountMax) && isNull(amountMin)) {
				hSql += " and t.amount<=:amountMax";
			} else if (!isNull(amountMin) && isNull(amountMax)) {
				hSql += " and t.amount>=:amountMin";
			} else {
				hSql += " and t.amount<=:amountMax and t.amount>=:amountMin";
			}
		// 自定义查询范围
		if (!isNullDate)
			// 自定义
			if (!isNull(endDate) && isNull(beginDate)) {
				hSql += " and t.updateTime<=:endDate";
			} else if (!isNull(beginDate) && isNull(endDate)) {
				hSql += " and t.updateTime>=:beginDate";
			} else {
				hSql += " and t.updateTime<=:endDate and t.updateTime>=:beginDate";
			}
		// 查询范围
		if (!isNulllastDate) {
			hSql += " and t.updateTime<=:nowDate and t.updateTime>=:lastDateValue";
		}
		// 交易状态
		if (!isNulltransStatusID)
			hSql += " and t.transStatus=:transStatusID";

		// 去掉待确认，等待银行确认
		hSql += " and t.transStatus<>"
				+ Constant.TRANS_STATUS_WAITEBANKFEEDBACK;

		// edited by 吴波 20080327网关商户 结算 交易明细查询
		if (StringUtils.isNotBlank(returnTrans)
				&& StringUtils.isNotBlank(transStatus)) {
			hSql += " and (t.transStatus =:transStatus or t.transStatus =:returnTrans)";
		}
		if (StringUtils.isNotBlank(gateWayBeginDate)) {
			hSql += " and t.endDate>=:gateWayBeginDate";
		}
		if (StringUtils.isNotBlank(gateWayEndDate)) {
			hSql += " and t.endDate<=:gateWayEndDate";
		}
		if (typeList != null) {
			hSql += " and t.actionType.id in(3,5,6,12,18,19,20)";
		}
		// order by
		// hSql += " order by t.updateTime desc,t.transId desc";
		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(hSql);

		if (!isNullId)
			q.setParameter("id", new Long(id));

		if (!isNullSubAcountID)
			q.setParameter("subAccountID", new Long(subAccountID));

		if (!isNullactionTypeID)
			q.setParameter("actionTypeID", new Long(actionTypeID));

		// 交易对方
		if (!isNullotherAccount)
			q.setParameter("debitID", "%" + otherAccountName + "%");
		// 显示收付款0收1付
		// if(!isNullamountView)
		// q.setParameter("amountView","3");

		if (!isNullTransId)
			q.setParameter("transId", "%" + transId + "%");

		if (!isNullOrderId)
			q.setParameter("orderId", "%" + orderId + "%");

		if (!isNullamount) {
			if (!isNull(amountMin))
				q.setParameter("amountMin", new Double(amountMin));
			if (!isNull(amountMax))
				q.setParameter("amountMax", new Double(amountMax));
		}
		// 自定义
		if (!isNullDate) {
			// System.err.println(("2006-04-18"+" 00:00:01"));
			// System.err.println(("2006-04-18"+" 23:59:59"));

			// System.err.println(DateUtil.string2Date(endDate+" 23:59:59"));
			// Date d =DateUtil.string2Date("2006-1-18"+"
			// "+"23:59:59","yyyy-MM-dd HH:mm:ss");
			// DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			// System.err.println(df.format(d));

			String format = "yyyy-MM-dd HH:mm:ss";
			if (!isNull(endDate)) {
				q.setParameter("endDate",
						DateUtil.string2Date(endDate + " 23:23:59", format));
			}
			if (!isNull(beginDate)) {
				q.setParameter("beginDate",
						DateUtil.string2Date(beginDate + " 00:00:00", format));
			}
		}
		if (!isNulllastDate) {
			q.setParameter("nowDate", nowDate);

			// 本周
			if (lastDate.equals("1"))
				q.setParameter("lastDateValue", DateUtil.getWeek(nowDate));

			// 本月
			else if (lastDate.equals("2"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate));

			// 3个月
			else if (lastDate.equals("3"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate, 3));

			// 6个月
			else if (lastDate.equals("4"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate, 6));

			// 一年
			else if (lastDate.equals("5"))
				q.setParameter("lastDateValue", DateUtil.getYear(nowDate));

		}
		if (!isNulltransStatusID)
			q.setParameter("transStatusID", transStatusID);
		// edited by wubo 20080327
		String format = "yyyy-MM-dd HH:mm:ss";
		if (StringUtils.isNotBlank(gateWayBeginDate)) {
			q.setParameter("gateWayBeginDate",
					DateUtil.string2Date(gateWayBeginDate, format));
		}
		if (StringUtils.isNotBlank(gateWayEndDate)) {
			q.setParameter("gateWayEndDate",
					DateUtil.string2Date(gateWayEndDate, format));
		}
		// if (typeList != null) {
		// q.setParameter("typeList", param.get("typeList"));
		// }
		if (StringUtils.isNotBlank(returnTrans)
				&& StringUtils.isNotBlank(transStatus)) {
			q.setParameter("returnTrans", returnTrans);
			q.setParameter("transStatus", transStatus);
		}
		int count = ((Long) q.uniqueResult()).intValue();
		return count;

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#getAccountHisQueryList(java
	 * .util.Map, int, int)
	 */
	public List getAccountHisQueryList(Map param, int pageNo, int recordPerPage) {

		// 交易对方
		String id = getParam(param, "accountID"); // 此值必须有
		String name = getParam(param, "accountName");

		String subAccountID = getParam(param, "subAccountID");

		// Long subAccountID = new Long(getParam(param, "subAccountID"));
		String actionTypeID = getParam(param, "actionTypeID");
		// 交易对方
		String otherAccountName = getParam(param, "otherAccountID");
		// 交易号
		String transId = getParam(param, "transId");
		// 是否显示订单编号
		boolean showOrderId = (Boolean) param.get("showOrderId") == null ? false
				: ((Boolean) param.get("showOrderId")).booleanValue();
		// 订单编号
		String orderId = getParam(param, "orderId");
		// 显示收款付款
		String amountView = getParam(param, "amountView");// 0收1付
		// 交易金额范围
		String amountMin = getParam(param, "amountMin");
		String amountMax = getParam(param, "amountMax");

		String lastDate = getParam(param, "lastDate"); // 查询范围
		String beginDate = getParam(param, "beginDate");// 开始时间
		String endDate = getParam(param, "endDate"); // 结束时间

		String dateType = getParam(param, "dateType");// 类型切换

		String transStatusID = getParam(param, "transStatusID");// 交易状态
		// edited by 吴波 20080327网关商户 结算 交易明细查询
		String gateWayBeginDate = getParam(param, "gateWayBeginDate");// 开始时间
		String gateWayEndDate = getParam(param, "gateWayEndDate"); // 结束时间
		List typeList = (List) param.get("typeList");
		String returnTrans = getParam(param, "returnTrans");
		String transStatus = getParam(param, "transStatus");
		Date nowDate = new Date();
		Date lastDateValue = null;
		// id="1";
		// subAccountID="1";
		// actionTypeID="1";
		// debitID="7";
		// transId ="test";
		// amountView="0";
		// amountMin=new Double("0");
		// amountMax=new Double("1000");

		boolean isNullId = isNull(id);
		boolean isNullSubAcountID = isNull(subAccountID);
		boolean isNullactionTypeID = isNull(actionTypeID);
		boolean isNullotherAccount = isNull(otherAccountName);
		boolean isNullamountView = isNull(amountView);
		boolean isNullTransId = isNull(transId);
		boolean isNullOrderId = isNull(orderId) || !showOrderId;
		boolean isNullamount = isNull(amountMin) && isNull(amountMax);
		// 自定义
		// boolean isNullDate = dateType == null || !dateType.equals("1")
		// || isNull(endDate) || isNull(beginDate);

		boolean isNullDate = isNull(endDate) && isNull(beginDate);
		// 查询范围
		// boolean isNulllastDate = !(dateType.equals("0") && !isNull(lastDate)
		// && !lastDate
		// .equals("0"));
		boolean isNulllastDate = !(!isNull(lastDate) && !lastDate.equals("0"));
		boolean isNulltransStatusID = isNull(transStatusID);

		// String hSql = " select new
		// cn.agiledata.bank.myspace.bean.AccountQueryBean(s, a, t) from "
		// + " SubAccount s ,Account a ,Transaction t "
		// + "where (t.credit.id=s.id and s.account.id=a.id";
		//
		// // 子账户
		// if (!isNullSubAcountID)
		// { // Long subaccountid = new Long(subAccountID);
		// hSql += " and (t.credit.id=:subAccountID and s.id=:subAccountID) " ;}
		//
		// if (!isNullId)
		// hSql += " and a.id=:id";
		//
		// hSql += " and (exists(select 1 from SubAccount sa, Account c " +
		// "where sa.account.id=c.id ";
		// if (!isNullotherAccount)
		// hSql += " and (c.email like :debitID or c.mobile like :debitID) " ;
		//
		// hSql += "and t.debit.id=sa.id)) or t.debit.id=s.id and
		// s.account.id=a.id " ;
		//
		// if (!isNullSubAcountID)
		// hSql += "and (t.debit.id=:subAccountID and s.id=:subAccountID)";
		//
		// if (!isNullId)
		// hSql += " and a.id=:id";
		//
		// hSql += " and (exists(select 1 from SubAccount sa, Account c " +
		// "where sa.account.id=c.id ";
		//
		// // 交易对方
		// if (!isNullotherAccount)
		// hSql += "and c.email like :debitID ";
		//
		// hSql +="and t.credit.id=sa.id and s.account.id!=c.id)))";
		String hSql = " select new cn.agiledata.bank.myspace.bean.AccountQueryBean(s, t) from "
				+ " SubAccount s, Transaction t "
				+ "where (t.credit.id=s.id  or t.debit.id=s.id)";

		if (!isNullId)
			hSql += " and s.account.id=:id";
		// 子账户
		if (!isNullSubAcountID) {
			hSql += " and s.id=:subAccountID ";
		}

		hSql += " and not exists(select 1 from SubAccount sa, SubAccount sb "
				+ "where sa.account.id=:id and sb.account.id=:id and sa.id>sb.id and sa.id=s.id";

		if (!isNullSubAcountID) {
			hSql += " and sb.id=:subAccountID ";
		}
		hSql += " and sa.id=t.credit.id and sb.id=t.debit.id) and exists (select 1 from "
				+ "SubAccount sa, Account a, SubAccount sb where ((t.credit.id=sa.id and t.debit.id=sb.id)"
				+ "or (t.debit.id=sa.id and t.credit.id=sb.id)) and sb.account.id=a.id and sa.id<>sb.id";

		// 交易对方
		if (!isNullotherAccount)
			hSql += " and a.name like :debitID ";

		hSql += " and ((a.id<>s.account.id) or (a.id=s.account.id and sa.account.id=sb.account.id))) ";

		// 交易类型
		if (!isNullactionTypeID)
			hSql += " and t.actionType.id=:actionTypeID";

		// 交易号
		if (!isNullTransId)
			hSql += " and t.transId like :transId ";

		// 订单编号
		if (!isNullOrderId)
			hSql += " and t.orderId like :orderId ";

		// 显示收付款
		if (!isNullamountView) {
			// 收
			if (amountView.equals("1"))
				hSql += " and t.credit.id=s.id ";
			// 付
			else if (amountView.equals("0"))
				hSql += " and t.debit.id=s.id ";
		}

		// 交易金额
		if (!isNullamount)
			if (!isNull(amountMax) && isNull(amountMin)) {
				hSql += " and t.amount<=:amountMax";
			} else if (!isNull(amountMin) && isNull(amountMax)) {
				hSql += " and t.amount>=:amountMin";
			} else {
				hSql += " and t.amount<=:amountMax and t.amount>=:amountMin";
			}

		// 自定义查询范围
		if (!isNullDate)
			// 自定义
			if (!isNull(endDate) && isNull(beginDate)) {
				hSql += " and t.updateTime<=:endDate";
			} else if (!isNull(beginDate) && isNull(endDate)) {
				hSql += " and t.updateTime>=:beginDate";
			} else {
				hSql += " and t.updateTime<=:endDate and t.updateTime>=:beginDate";
			}

		// 查询范围
		if (!isNulllastDate) {
			hSql += " and t.updateTime<=:nowDate and t.updateTime>=:lastDateValue";
		}
		// 交易状态
		if (!isNulltransStatusID)
			hSql += " and t.transStatus=:transStatusID";

		// 去掉待确认，等待银行确认
		hSql += " and t.transStatus<>"
				+ Constant.TRANS_STATUS_WAITEBANKFEEDBACK;

		// edited by 吴波 20080327网关商户 结算 交易明细查询
		if (StringUtils.isNotBlank(returnTrans)
				&& StringUtils.isNotBlank(transStatus)) {
			hSql += " and (t.transStatus =:transStatus or t.transStatus =:returnTrans)";
		}
		if (StringUtils.isNotBlank(gateWayBeginDate)) {
			hSql += " and t.endDate>=:gateWayBeginDate";
		}
		if (StringUtils.isNotBlank(gateWayEndDate)) {
			hSql += " and t.endDate<=:gateWayEndDate";
		}
		if (typeList != null) {
			hSql += " and t.actionType.id in (3,5,6,12,18,19,20)";
		}
		// order by
		hSql += " order by t.updateTime desc,t.transId desc";
		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(hSql);

		if (!isNullId)
			q.setParameter("id", new Long(id));

		if (!isNullSubAcountID)
			q.setParameter("subAccountID", new Long(subAccountID));

		if (!isNullactionTypeID)
			q.setParameter("actionTypeID", new Long(actionTypeID));

		// 交易对方
		if (!isNullotherAccount)
			q.setParameter("debitID", "%" + otherAccountName + "%");

		// 显示收付款0收1付
		// if(!isNullamountView)
		// q.setParameter("amountView","3");

		if (!isNullTransId)
			q.setParameter("transId", "%" + transId + "%");

		if (!isNullOrderId)
			q.setParameter("orderId", "%" + orderId + "%");

		if (!isNullamount) {
			if (!isNull(amountMin))
				q.setParameter("amountMin", new Double(amountMin));
			if (!isNull(amountMax))
				q.setParameter("amountMax", new Double(amountMax));
		}
		// 自定义
		if (!isNullDate) {
			// System.err.println(("2006-04-18"+" 00:00:01"));
			// System.err.println(("2006-04-18"+" 23:59:59"));

			// System.err.println(DateUtil.string2Date(endDate+" 23:59:59"));
			// Date d =DateUtil.string2Date("2006-1-18"+"
			// "+"23:59:59","yyyy-MM-dd HH:mm:ss");
			// DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			// System.err.println(df.format(d));

			String format = "yyyy-MM-dd HH:mm:ss";
			if (!isNull(endDate)) {
				q.setParameter("endDate",
						DateUtil.string2Date(endDate + " 23:23:59", format));
			}
			if (!isNull(beginDate)) {
				q.setParameter("beginDate",
						DateUtil.string2Date(beginDate + " 00:00:00", format));
			}
		}

		if (!isNulllastDate) {
			q.setParameter("nowDate", nowDate);

			// 本周
			if (lastDate.equals("1"))
				q.setParameter("lastDateValue", DateUtil.getWeek(nowDate));

			// 本月
			else if (lastDate.equals("2"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate));

			// 3个月
			else if (lastDate.equals("3"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate, 3));

			// 6个月
			else if (lastDate.equals("4"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate, 6));

			// 一年
			else if (lastDate.equals("5"))
				q.setParameter("lastDateValue", DateUtil.getYear(nowDate));

		}
		if (!isNulltransStatusID)
			q.setParameter("transStatusID", transStatusID);

		// 分页支持
		if (pageNo >= 0) {
			pageNo--;
			q.setFirstResult(pageNo * recordPerPage);
			q.setMaxResults(recordPerPage);

		}
		// edited by wubo 20080327
		String format = "yyyy-MM-dd HH:mm:ss";
		if (StringUtils.isNotBlank(gateWayBeginDate)) {
			q.setParameter("gateWayBeginDate",
					DateUtil.string2Date(gateWayBeginDate, format));
		}
		if (StringUtils.isNotBlank(gateWayEndDate)) {
			q.setParameter("gateWayEndDate",
					DateUtil.string2Date(gateWayEndDate, format));
		}
		// if (typeList != null) {
		// q.setParameter("typeList", param.get("typeList"));
		// }
		if (StringUtils.isNotBlank(returnTrans)
				&& StringUtils.isNotBlank(transStatus)) {
			q.setParameter("returnTrans", returnTrans);
			q.setParameter("transStatus", transStatus);
		}
		List l = q.list();
		return l;

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#getAccountHisCount(java.
	 * util.Map)
	 */
	public int getAccountHisCount(Map parameter) {

		List list = getAccountHisQueryList(parameter, -1, -1);
		return list.size();
	}

	private String getParam(Map map, String key) {
		if (map == null)
			return null;
		if (map.get(key) == null)
			return null;

		return (String) map.get(key);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * cn.agiledata.bank.common.dao.AccountTemplateInter#isNull(java.lang.Object)
	 */
	public boolean isNull(Object o) {
		return o == null || o.toString().length() == 0;
	}

	public List getPeerAccountListById(String id) {
		List resultList = getHibernateTemplate()
				.find("select distinct a.peerAccount.email,a.peerAccount.mobile,a.peerAccount.name,a.source,a.updateTime from PeerInnerAccount a where a.account.id =? order by a.source desc,a.updateTime desc",
						new Object[] { new Long(id) });
		// getHibernateTemplate().flush();
		return resultList;
	}

	public String validateAccount(String email) {
		if (!ifExistAccount(email)) { // 不存在该账户（只考虑类型为个人和企业的账户，内部账户认为不存在）
			return "-1";
		}

		// return getAccountNameByName(email);

		Account account = getAccountByName(email);

		// 校验email或mobile的状态，如果不是normal状态，返回"-1"，表示账户不存在
		if (StringUtil.isEmail(email)) {
			if (!account.getEmStatus().equals(Constant.ACCOUNT_STATE_NORMAL)) {
				return "-1";
			}
		} else {
			if (!account.getMobStatus().equals(Constant.ACCOUNT_STATE_NORMAL)) {
				return "-1";
			}
		}

		// 账户存在且指定的email或mobile状态正常，返回该账户的账户名(如果账户名为null则返回空字符串)
		return StringUtil.getNotNullValue(account.getName());
	}

	/**
	 * 判断这个用户是不是企业用户，如果是企业用户则返回是不是能向个人支付，如果是个人用户返回可以支付
	 * 
	 * @param email
	 * @return
	 */
	public String b2cavaiable(String email) {
		if (StringUtil.isStrEmpty(email))
			return "-1";
		if (!ifExistAccount(email)) { // 不存在该账户
			return "-1";
		}
		Account account = getAccountByName(email);
		if (account.getAccountCompany() != null)
			return account.getAccountCompany().getB2cable();
		else
			return Constant.B2C_AVAIABLE;
	}

	public boolean ifExistAccount(String email) {
		// TODO Auto-generated method stub
		if (email == null || email.equals("")) {
			return true;
		}
		Account acco = WeatherUtil.getAccountByEmail(email);
		if (acco != null) {
			// 不包括内部账户，即根据参数email在数据库里找到了匹配的账户，且该账户不是内部账户，返回true
			if (!acco.getAccountType().equals(Constant.ACCOUNT_TYPE_INITIAL))
				return true;
		}
		return false;

	}

	public int ifExistInPeerHis(String email, int id) {
		if (!ifExistAccount(email))
			return -1;
		if (email == null || email.equals(""))
			return -2;
		Account acco = WeatherUtil.getAccountByEmail(email);
		PeerInnerAccount pah = getPeerAcctHis(acco.getId().toString(),
				Integer.toString(id), "1");
		if (pah != null)
			return 1;
		return 0;
	}

	public SubAccount getJ2MESubAccount(String AccountId) {
		List list = this.getHibernateTemplate().find(
				"from SubAccount "
						+ "t where t.account.id=? and t.subAccountType = 1",
				new Object[] { AccountId });
		if (list != null && list.size() > 0) {
			SubAccount sub = (SubAccount) list.get(0);
			return sub;
		} else {
			return null;
		}
	}

	public void removePeerAccount(String acctID) {
		PeerInnerAccount pah = getPeerAcctHisByID(acctID);
		if (pah != null)
			getHibernateTemplate().delete(pah);

	}

	public PeerInnerAccount getPeerAcctHisByID(String id) {
		String hstr = "from PeerInnerAccount where id=?";
		List ls = getHibernateTemplate().find(hstr,
				new Object[] { new Long(id) });
		if (ls != null && ls.size() > 0)
			return (PeerInnerAccount) ls.get(0);
		else
			return null;
	}

	public PeerInnerAccount addAccountInPeer(Account peerAcct, String acctSeq,
			String type) {
		PeerInnerAccount acctHis = new PeerInnerAccount();
		acctHis.setAccount(getAccount(acctSeq));
		acctHis.setPeerAccount(peerAcct);
		acctHis.setSource(type);
		acctHis.setUpdateTime(new Date());
		acctHis.setUpdateUser(acctSeq);
		acctHis.setCreateTime(acctHis.getUpdateTime());
		acctHis.setCreateUser(acctHis.getUpdateUser());
		return acctHis;
	}

	public String getNameByEmail(String email) {
		Account acct = WeatherUtil.getAccountByEmailAvaible(email);
		if (acct != null)
			return acct.getName();
		else
			return null;
	}

	public List getSubAccountsByAccountId(String id) {
		String sqlStr = "from SubAccount t where t.account.id = ?"
				+ " and t.validFlag = ? and t.subAccountType = '0'";
		return this.getHibernateTemplate().find(sqlStr,
				new Object[] { new Long(id), Constant.VALID });
	}

	public List getAllSubAccountsByMainId(String id) {
		String sqlStr = "from SubAccount t where t.account.id = ?";
		return this.getHibernateTemplate().find(sqlStr,
				new Object[] { new Long(id) }); // 测试时候报告could
		// not
		// bind
		// value
		// '461'
		// to
		// parameter:
		// 1;
		// java.lang.String
	}

	public List getAccountHisList(String subAccountId) {
		String sqlStr = "from AcctBalHis t where t.subAcctSeq = ?"
				+ " order by t.updateTime desc";
		return this.getHibernateTemplate().find(sqlStr,
				new Object[] { new Long(subAccountId) });
	}

	public List getExistDefaultBank() {
		String hql = " select distinct v.bankInfo from VnvAccount v where v.defaultFlag = 'Y' and v.status='Y' ";
		return this.getHibernateTemplate().find(hql);
	}

	public SubAccount getSubAccountByEmailMobile(String msg) {
		String hql = " from Account t where t.email = '" + msg
				+ "' or t.mobile = '" + msg + "'";
		Account account = (Account) this.getHibernateTemplate().find(hql)
				.get(0);
		return account.getDefaultSubAccount();
	}

	public int getAcctBalHisCount(Map param) {

		// String id = getParam(param, "accountID"); //此值必须有
		Long id = new Long(getParam(param, "accountID"));
		String subAccountID = getParam(param, "subAccountID");

		String lastDate = getParam(param, "lastDate"); // 查询范围
		String beginDate = getParam(param, "beginDate");// 开始时间
		String endDate = getParam(param, "endDate"); // 结束时间

		String dateType = getParam(param, "dateType");// 类型切换
		Date nowDate = new Date();
		Date lastDateValue = null;
		boolean isNullId = isNull(id);
		boolean isNullSubAcountID = isNull(subAccountID);
		boolean isNullDate = dateType == null || !dateType.equals("1")
				|| isNull(endDate) || isNull(beginDate);
		// 查询范围
		boolean isNulllastDate = !(dateType.equals("0") && !isNull(lastDate) && !lastDate
				.equals("0"));
		String hSql = " select count(*) as count from "
				+ " AcctBalHis a ,SubAccount s   "
				+ "where (a.subAcctSeq = s.id ) ";
		if (!isNullId) {

			hSql += " and s.account.id =:id";
		}
		if (StringUtils.isNotEmpty(beginDate)) {
			hSql += " and a.updateTime>=:beginDate";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			hSql += " and a.updateTime<=:endDate";
		}
		// 子账户
		if (!isNullSubAcountID)
			hSql += " and (a.subAcctSeq=:subAcountID )";
		hSql += " and a.amount != '0.00' ";
		// if (!isNullDate)
		// 自定义
		// hSql += " and a.updateTime<=:endDate and a.updateTime>=:beginDate";

		// 查询范围
		// if (!isNulllastDate) {
		// hSql += " and a.updateTime<=:nowDate and
		// a.updateTime>=:lastDateValue";
		// }
		// hSql += " order by a.updateTime desc";
		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(hSql);

		if (!isNullId)
			q.setParameter("id", id);

		if (!isNullSubAcountID) {

			q.setParameter("subAcountID", new Long(subAccountID));
		}
		// if (!isNullDate) {
		// System.err.println(("2006-04-18"+" 00:00:01"));
		// System.err.println(("2006-04-18"+" 23:59:59"));

		// System.err.println(DateUtil.string2Date(endDate+" 23:59:59"));
		// Date d =DateUtil.string2Date("2006-1-18"+" "+"23:59:59","yyyy-MM-dd
		// HH:mm:ss");
		// DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		// System.err.println(df.format(d));

		String format = "yyyy-MM-dd HH:mm:ss";

		if (StringUtils.isNotEmpty(endDate))
			q.setParameter("endDate",
					DateUtil.string2Date(endDate + " 23:23:59", format));
		if (StringUtils.isNotEmpty(beginDate))
			q.setParameter("beginDate",
					DateUtil.string2Date(beginDate + " 00:00:00", format));
		// }

		if (!isNulllastDate) {
			q.setParameter("nowDate", nowDate);

			// 本周
			if (lastDate.equals("1"))
				q.setParameter("lastDateValue", DateUtil.getWeek(nowDate));

			// 本月
			else if (lastDate.equals("2"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate));

			// 3个月
			else if (lastDate.equals("3"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate, 3));

			// 6个月
			else if (lastDate.equals("4"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate, 6));

			// 一年
			else if (lastDate.equals("5"))
				q.setParameter("lastDateValue", DateUtil.getYear(nowDate));
		}

		int count = ((Long) q.uniqueResult()).intValue();

		return count;

	}

	public List getAcctBalHisList(Map param, int pageNo, int recordPerPage) {
		// String id = getParam(param, "accountID"); //此值必须有
		Long id = new Long(getParam(param, "accountID"));
		Object subAccountID = getParam(param, "subAccountID");
		// Long subAccountID = new Long(getParam(param, "subAccountID"));

		String lastDate = getParam(param, "lastDate"); // 查询范围
		String beginDate = getParam(param, "beginDate");// 开始时间
		String endDate = getParam(param, "endDate"); // 结束时间

		String dateType = getParam(param, "dateType");// 类型切换
		Date nowDate = new Date();
		Date lastDateValue = null;
		boolean isNullId = isNull(id);
		boolean isNullSubAcountID = isNull(subAccountID);
		boolean isNullDate = dateType == null || !dateType.equals("1")
				|| isNull(endDate) || isNull(beginDate);
		// 查询范围
		boolean isNulllastDate = !(dateType.equals("0") && !isNull(lastDate) && !lastDate
				.equals("0"));
		String hSql = " select new cn.agiledata.bank.myspace.bean.AcctBalHisBean(a,s,sa,t)  from "
				+ " AcctBalHis a ,SubAccount sa,SubAccount s ,Transaction t "
				+ "where (a.subAcctSeq = s.id ) and (a.peerSeq = sa.id) and(a.transid = t.transId)";
		if (!isNullId)
			hSql += " and s.account.id =:id";
		// 子账户
		if (!isNullSubAcountID)
			hSql += " and (a.subAcctSeq=:subAcountID )";
		if (StringUtils.isNotEmpty(beginDate)) {
			hSql += " and a.updateTime>=:beginDate";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			hSql += " and a.updateTime<=:endDate";
		}
		/*
		 * if (!isNullDate) // 自定义 hSql += " and a.updateTime<=:endDate and
		 * a.updateTime>=:beginDate";
		 */

		// 查询范围
		if (!isNulllastDate) {
			hSql += " and a.updateTime<=:nowDate and a.updateTime>=:lastDateValue";
		}
		hSql += " and a.amount != '0.00' ";
		hSql += " order by a.updateTime desc, a.transid desc";
		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(hSql);

		if (!isNullId)
			q.setParameter("id", id);

		Long subId = new Long(1);
		if (!isNullSubAcountID) {
			subId = new Long((String) subAccountID);
			q.setParameter("subAcountID", subId);
		}
		// if (!isNullDate) {
		// System.err.println(("2006-04-18"+" 00:00:01"));
		// System.err.println(("2006-04-18"+" 23:59:59"));

		// System.err.println(DateUtil.string2Date(endDate+" 23:59:59"));
		// Date d =DateUtil.string2Date("2006-1-18"+" "+"23:59:59","yyyy-MM-dd
		// HH:mm:ss");
		// DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		// System.err.println(df.format(d));

		String format = "yyyy-MM-dd HH:mm:ss";
		if (StringUtils.isNotEmpty(endDate))
			q.setParameter("endDate",
					DateUtil.string2Date(endDate + " 23:23:59", format));
		if (StringUtils.isNotEmpty(beginDate))
			q.setParameter("beginDate",
					DateUtil.string2Date(beginDate + " 00:00:00", format));
		// }
		if (!isNulllastDate) {
			q.setParameter("nowDate", nowDate);

			// 本周
			if (lastDate.equals("1"))
				q.setParameter("lastDateValue", DateUtil.getWeek(nowDate));

			// 本月
			else if (lastDate.equals("2"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate));

			// 3个月
			else if (lastDate.equals("3"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate, 3));

			// 6个月
			else if (lastDate.equals("4"))
				q.setParameter("lastDateValue", DateUtil.getMonth(nowDate, 6));

			// 一年
			else if (lastDate.equals("5"))
				q.setParameter("lastDateValue", DateUtil.getYear(nowDate));
		}
		if (pageNo >= 0) {
			pageNo--;
			q.setFirstResult(pageNo * recordPerPage);
			q.setMaxResults(recordPerPage);

		}
		List l = q.list();

		return l;

	}

	// 获取指定用户指定时间点之前的各子账户余额的和，如果不指定时间点，不设置查询时的时间条件
	public BigDecimal getTotalAccountBalance(Long accountId, Date beforeDate) {
		BigDecimal totalAccountBalance = new BigDecimal(0);
		List subAccountList = getSubAccountsByAccountId(accountId.toString());
		for (Iterator iter = subAccountList.iterator(); iter.hasNext();) {
			SubAccount subAccount = (SubAccount) iter.next();
			totalAccountBalance = totalAccountBalance.add(getSubAccountBalance(
					subAccount.getId(), beforeDate));
		}

		return totalAccountBalance;
	}

	// 获取指定子账户指定时间点之前的可用余额，如果不指定时间点，不设置查询时的时间条件
	public BigDecimal getSubAccountBalance(Long subAccountId, Date beforeDate) {
		String hql = "select a.balance from AcctBalHis a, SubAccount s where a.subAcctSeq=s.id";
		hql += " and s.id=:id and s.subAccountType='0' and s.validFlag='Y'";
		if (beforeDate != null) {
			hql += " and a.updateTime<:beforeDate";
		}
		hql += " order by a.updateTime desc";

		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(hql);
		q.setParameter("id", subAccountId);
		if (beforeDate != null) {
			q.setParameter("beforeDate", beforeDate);
		}

		List l = q.list();
		Double bal = new Double(0.0);
		if (l != null && l.size() != 0) {
			bal = (Double) l.get(0);
		}

		return new BigDecimal(bal.doubleValue());
	}

	/***************************************************************************
	 * 计算规定期限内的充值金额
	 * 
	 * @param accountId
	 * @param date
	 * @return
	 */
	public Double getAmountByTransaction(String accountId, String date) {
		String hql = "select sum(t.amount) from Transaction t where t.credit.id in "
				+ "(select s.id from SubAccount s where s.account.id = :accountId and s.validFlag='Y') "
				+ "and t.actionType.id in ('1','11') and t.transStatus='3' and t.updateTime>=:beginDate ";
		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(hql);
		String format = "yyyy-MM-dd HH:mm:ss";
		q.setParameter("accountId", new Long(accountId));
		q.setParameter("beginDate",
				DateUtil.string2Date(date + " 23:59:59", format));

		return (Double) q.uniqueResult();
	}

	/***************************************************************************
	 * 计算规定天数内的收款金额
	 * 
	 * @param accountId
	 * @param date
	 * @return
	 */
	public Double getRevenueByTransaction(String accountId, String date) {
		String hql = "select sum(t.amount - t.creditFee) from Transaction t where t.credit.id in "
				+ "(select s.id from SubAccount s where s.account.id = :accountId and s.validFlag='Y') "
				+ "and t.actionType.id in ('2','3','6') and t.transStatus='3' and t.updateTime>=:beginDate ";
		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(hql);
		String format = "yyyy-MM-dd HH:mm:ss";
		q.setParameter("accountId", new Long(accountId));
		q.setParameter("beginDate",
				DateUtil.string2Date(date + " 23:59:59", format));

		return (Double) q.uniqueResult();
	}

	/***************************************************************************
	 * 计算规定天数内的提现金额
	 * 
	 * @param accountId
	 * @param date
	 * @return
	 */
	public Double getDrawByTransaction(String accountId, String date) {
		String hql = "select sum(t.amount - t.debitFee) from Transaction t where t.debit.id in "
				+ "(select s.id from SubAccount s where s.account.id = :accountId and s.validFlag='Y') "
				+ "and t.actionType.id = '4' and t.transStatus='3' and t.updateTime>=:beginDate ";
		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(hql);
		String format = "yyyy-MM-dd HH:mm:ss";
		q.setParameter("accountId", new Long(accountId));
		q.setParameter("beginDate",
				DateUtil.string2Date(date + " 23:59:59", format));

		return (Double) q.uniqueResult();
	}

	/***************************************************************************
	 * 计算限制天数外的账户余额
	 * 
	 * @param accountId
	 * @param date
	 * @return
	 */

	public Double getAccBalHis(Set subAccounts, String date) {
		double totAccBal = 0.00;
		for (Iterator iter = subAccounts.iterator(); iter.hasNext();) {
			SubAccount subAccount = (SubAccount) iter.next();
			totAccBal = totAccBal
					+ getTotAccBal(subAccount.getId().toString(), date)
							.doubleValue();
		}
		return new Double(totAccBal);
	}

	private Double getTotAccBal(String subAccountId, String date) {
		String hql = "select a.balance from AcctBalHis a, SubAccount s where a.subAcctSeq=s.id "
				+ "and s.id=:id and s.subAccountType='0' "
				+ "and s.validFlag='Y' and a.updateTime<:beginDate order by a.updateTime desc";
		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(hql);
		String format = "yyyy-MM-dd HH:mm:ss";
		q.setParameter("id", new Long(subAccountId));
		q.setParameter("beginDate",
				DateUtil.string2Date(date + " 23:59:59", format));
		List l = q.list();
		if (l != null && l.size() != 0) {
			return (Double) l.get(0);
		}
		return new Double(0.0);
	}

	public Sysbook getDateRestrictions() {
		return (Sysbook) getHibernateTemplate().find(
				"" + "from Sysbook where id.typeId='DATE_RESTRICTIONS'").get(0);
	}

	public PageBean getAcctBalHisPageBean(Map param, int pageNo,
			int recordPerPage) {
		PageBean pb = new PageBean(getAcctBalHisCount(param), pageNo,
				recordPerPage);
		pb.setData(getAcctBalHisList(param, pageNo, recordPerPage));
		return pb;
	}

	public void saveAcctHis(AccountHis acctHis) {
		// TODO Auto-generated method stub
		getHibernateTemplate().save(acctHis);

	}

	/**
	 * 提供所有子账户，以便逐一为其结算利息; 非内部账户之有效、普通用户(非离线)； Account.countType!='C'
	 * SubAccount.ValidFlag=='Y' SubAccount.SubAccountType=='0'
	 * 
	 * @author johnny
	 */
	final static String HQL_GETALLSUBACCOUNT4INTEREST = "from SubAccount s where s.account.accountType<>'"
			+ Constant.ACCOUNT_TYPE_INITIAL
			+ "' and s.validFlag='Y' and s.subAccountType=0";

	public List getAllSubAccount4Interest() {
		try {
			HibernateTemplate ht = this.getHibernateTemplate();
			List list = ht.find(HQL_GETALLSUBACCOUNT4INTEREST);
			return list;
		} catch (DataAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		}
	}

	/**
	 * * 提供所有子账户，以便逐一为其结算利息; 非内部账户之有效、普通用户(非离线)； Account.countType!='C'
	 */
	public List getAllAccount4Interest() {
		// TODO Auto-generated method stub
		String hql = "from Account a where a.accountType<>?";
		List resultList = getHibernateTemplate().find(hql,
				Constant.ACCOUNT_TYPE_INITIAL);
		return resultList;
	}

	public boolean isValidAccount(String accountId) {
		String hql = "from Account a where a.status="
				+ Constant.ACCOUNT_STATE_NORMAL + " and a.accountId=?";
		List resultList = getHibernateTemplate().find(hql, accountId);
		return resultList.size() == 1;
	}

	/**
	 * 根据account表的accountId得到账户对象
	 */
	public Account getAccountByAccountId(String id) {
		List result = getHibernateTemplate().find(
				"from Account a where a.accountId=?", new Object[] { id });
		if (result != null && result.size() > 0) {
			return (Account) result.get(0);
		}
		return null;
	}

	/**
	 * 根据account表的accountId得到IVR子账户对象
	 */
	public SubAccount getIvrSubAccountByAccountId(String id) {
		List result = getHibernateTemplate()
				.find("from SubAccount s where s.account.accountId=? and s.account.accountType<>? and s.validFlag=? and s.ivrFlag=?",
						new Object[] { id, Constant.ACCOUNT_TYPE_INITIAL,
								Constant.VALID, Constant.IVR_SUBACCOUNT_YES });
		if (result != null && result.size() > 0) {
			return (SubAccount) result.get(0);
		}
		return null;
	}

	/**
	 * 根据账户accountId得到该账户的默认子账户
	 * 
	 * @param id
	 * @return
	 */
	public SubAccount getDefaultSubAccountByAccountId(String id) {
		List result = getHibernateTemplate()
				.find("from SubAccount s where s.account.accountId=? and s.account.accountType<>? and s.validFlag=? and s.defaultFlag=?",
						new Object[] { id, Constant.ACCOUNT_TYPE_INITIAL,
								Constant.VALID, Constant.DETAIL_FLAG_TRUE });
		if (result != null && result.size() > 0) {
			return (SubAccount) result.get(0);
		}
		return null;
	}

	public void updateSubAccount(SubAccount sub) {
		getHibernateTemplate().update(sub);

	}

	public void update(AccountBalance accBalance) {
		// TODO Auto-generated method stub
		getHibernateTemplate().update(accBalance);
	}

	public List getAccountListByIdcardStatus(String idcardStatus) {
		String hql = "from Account a where a.idcardStatus=?";
		return getHibernateTemplate().find(hql, idcardStatus);
	}

	public List getAccountListByIdcardNo(String idcardNo) {
		String hql = "from Account a where a.idCode=?";
		return getHibernateTemplate().find(hql, idcardNo);
	}

	public Sysbook getDatePermit() {
		return (Sysbook) getHibernateTemplate()
				.find(""
						+ "from Sysbook where id.typeId='PERMIT_DAYS_OF_TRANSACTION'")
				.get(0);
	}

	public Sysbook getDatePermitById(String id) {
		return (Sysbook) getHibernateTemplate()
				.find(""
						+ "from Sysbook where id.typeId='PERMIT_DAYS_OF_TRANSACTION' and id.listId = "
						+ id).get(0);
	}

	public String ifHasEmail(String email) {
		String hql = "from Account a where a.email = '" + email + "'";
		List list = getHibernateTemplate().find(hql);
		if (list.size() > 0) {
			return "true";
		} else {
			return "false";
		}
	}

	public String isEmail(String email) {
		if (StringUtil.isEmail(email)) {
			return "true";
		} else {
			return "false";
		}
	}

	public String isMobile(String mobile) {
		if (StringUtil.isMobile(mobile)) {
			return "true";
		} else {
			return "false";
		}
	}

	public String isEmpty(String data) {
		if (StringUtil.isStrEmpty(data)) {
			return "true";
		} else {
			return "false";
		}
	}

	public String ifHasMobile(String mobile) {
		if (isMobile(mobile).equals("true")) {

			String hql = "from Account a where a.mobile = '" + mobile + "'";
			List list = getHibernateTemplate().find(hql);
			if (list.size() == 1) {
				return "true";
			} else {
				return "false";
			}
		} else {
			return "true";
		}
	}

	public SubAccount getReturnAccount() {
		return this.getSubAccount("12");
	}

	public Object[] sumAmountByTrans(Account account) {
		Object[] bdReturn = { new Double(0), new Long(0) };
		List resultList = getHibernateTemplate()
				.find("select sum(trans.amount),count(*) from Transaction trans where trans.credit.account.id=? and"
						+ " trans.transStatus='3' ",
						new Object[] { account.getId() });

		if (resultList != null && resultList.size() > 0) {
			bdReturn = (Object[]) resultList.get(0);
		}
		return bdReturn;
	}

	public PageBean getAllCreditTrans(Account account, int pageNo,
			int recordPerPage) {
		int intTotalCount = 0; // 总数
		Object[] arrSum = sumAmountByTrans(account);
		intTotalCount = ((Long) arrSum[1]).intValue();
		PageBean pb = new PageBean(intTotalCount, pageNo, recordPerPage);

		String strHql = "select new cn.agiledata.bank.myspace.bean.AccountHisQueryDetailBean(trans.credit.account, trans) "
				+ "from Transaction trans where trans.credit.account.id=:acctId and"
				+ " trans.transStatus='3' order by trans.updateTime desc ";
		Query q = getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(strHql);
		q.setParameter("acctId", account.getId());

		if (pageNo >= 0) {
			pageNo--;
			q.setFirstResult(pageNo * recordPerPage);
			q.setMaxResults(recordPerPage);

		}
		List resultList = q.list();
		pb.setData(resultList);
		return pb;
	}

	public String confirmToRule(String code, String amount) {
		String hql = "from MoneyRole m where m.code = '" + code
				+ "' and m.status='Y'";
		List list = getHibernateTemplate().find(hql);
		WebContext webContext = WebContextFactory.get();
		AccountSummary as = WeatherUtil.getLoingInfoFromSession(webContext
				.getHttpServletRequest());
		if (list.size() > 0) {
			MoneyRole moneyRole = (MoneyRole) getHibernateTemplate().find(hql)
					.get(0);
			if (Double
					.compare(moneyRole.getCodevalue(), Double.valueOf(amount)) < 0) {
				// 发送预警邮件
				VNVMail mail = VNVMail.createRuleMail2(this.email,
						moneyRole.getCodename(), amount, as.getAccountName());
				this.notificationService.sendMail(mail);
				return "true";
			} else {
				return "false";
			}
		} else {
			return "false";
		}
	}

	public String confirmToRuleByGateway(String amount) throws Exception {
		String sign = "";
		StringBuffer sb = new StringBuffer(
				"from MoneyRole m where m.code in ('05','55','11') and m.status='Y'");
		List list = getHibernateTemplate().find(sb.toString());
		// Account account = this.getAccountService().findAccountByEmail(
		// email);
		WebContext webContext = WebContextFactory.get();
		AccountSummary as = WeatherUtil.getLoingInfoFromSession(webContext
				.getHttpServletRequest());
		boolean isIinfract = false;
		sb.setLength(0);
		sb.append("select sum(t.amount) from Transaction t where t.actionType=6 and t.debit.id="
				+ as.getDefaultSubAccountId() + " and t.transStatus=3");
		Calendar calendar = null;
		Date param2 = null;
		Date param3 = null;
		sb.append(" and t.createTime <= :end and t.createTime>= :start");
		if (list.size() > 0) {
			for (Object object : list) {
				MoneyRole moneyRole = (MoneyRole) object;
				if ("11".equals(moneyRole.getCode())
						&& Double.compare(moneyRole.getCodevalue(),
								Double.valueOf(amount)) <= 0) {
					sendMail(amount, moneyRole.getCodename(),
							as.getAccountName());
					saveLog(as, amount, moneyRole.getCodename());
					sign = "11";
					isIinfract = true;
				}
				if ("55".equals(moneyRole.getCode())) {
					calendar = Calendar.getInstance();
					calendar.set(Calendar.HOUR_OF_DAY, 0);
					calendar.set(Calendar.MINUTE, 0);
					calendar.set(Calendar.SECOND, 0);
					calendar.set(Calendar.MILLISECOND, 0);
					param2 = calendar.getTime();
					calendar.set(Calendar.HOUR_OF_DAY, 23);
					calendar.set(Calendar.MINUTE, 59);
					calendar.set(Calendar.SECOND, 59);
					calendar.set(Calendar.MILLISECOND, 999);
					param3 = calendar.getTime();
					Query q = getHibernateTemplate().getSessionFactory()
							.getCurrentSession().createQuery(sb.toString());
					q.setParameter("end", param3);
					q.setParameter("start", param2);
					Object ob = q.uniqueResult();
					Double val = ob == null ? 0d : (Double) ob;
					if (Double.compare(moneyRole.getCodevalue(), val) <= 0) {
						sendMail(amount, moneyRole.getCodename(),
								as.getAccountName());
						saveLog(as, amount, moneyRole.getCodename());
						sign = "55";
						isIinfract = true;
					}
				}
				if ("05".equals(moneyRole.getCode())) {
					calendar = Calendar.getInstance();
					calendar.set(Calendar.DAY_OF_MONTH, 1);
					calendar.set(Calendar.HOUR_OF_DAY, 0);
					calendar.set(Calendar.MINUTE, 0);
					calendar.set(Calendar.SECOND, 0);
					calendar.set(Calendar.MILLISECOND, 0);
					param2 = calendar.getTime();
					calendar.set(Calendar.DAY_OF_MONTH,
							calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
					calendar.set(Calendar.HOUR_OF_DAY, 23);
					calendar.set(Calendar.MINUTE, 59);
					calendar.set(Calendar.SECOND, 59);
					calendar.set(Calendar.MILLISECOND, 999);
					param3 = calendar.getTime();
					Query q = getHibernateTemplate().getSessionFactory()
							.getCurrentSession().createQuery(sb.toString());
					q.setParameter("end", param3);
					q.setParameter("start", param2);
					Object ob = q.uniqueResult();
					Double val = ob == null ? 0d : (Double) ob;
					if (Double.compare(moneyRole.getCodevalue(), val) <= 0) {
						sendMail(amount, moneyRole.getCodename(),
								as.getAccountName());
						saveLog(as, amount, moneyRole.getCodename());
						sign = "05";
						isIinfract = true;
					}
				}
				if (isIinfract) {
					break;
				}
			}
		}
		return sign;
	}

	public String confirmToRuleByInpour(String amount) throws Exception {
		String sign = "";
		try {
			StringBuffer sb = new StringBuffer(
					"from MoneyRole m where m.code in ('66','67','68','75') and m.status='Y'");
			List list = getHibernateTemplate().find(sb.toString());
			// Account account = this.getAccountService().findAccountByEmail(
			// email);
			WebContext webContext = WebContextFactory.get();
			AccountSummary as = WeatherUtil.getLoingInfoFromSession(webContext
					.getHttpServletRequest());
			boolean isIinfract = false;
			sb.setLength(0);
			sb.append("select sum(t.amount) from Transaction t where t.actionType=1 and t.credit.id="
					+ as.getDefaultSubAccountId() + " and t.transStatus=3");
			Calendar calendar = null;
			Date param2 = null;
			Date param3 = null;
			sb.append(" and t.createTime <= :end and t.createTime>= :start");
			if (list.size() > 0) {
				for (Object object : list) {
					MoneyRole moneyRole = (MoneyRole) object;
					if ("66".equals(moneyRole.getCode())
							&& Double.compare(moneyRole.getCodevalue(),
									Double.valueOf(amount)) <= 0) {
						sendMail(amount, moneyRole.getCodename(),
								as.getAccountName());
						saveLog(as, amount, moneyRole.getCodename());
						sign = "66";
						isIinfract = true;
					}
					if ("75".equals(moneyRole.getCode())
							&& compareAccountMoney(as.getAccount()
									.getDefaultSubAccount().getId().toString(),
									new Double(moneyRole.getCodevalue())) == 0) {
						sendMail(amount, moneyRole.getCodename(),
								as.getAccountName());
						saveLog(as, amount, moneyRole.getCodename());
						sign = "75";
						isIinfract = true;
					}
					if ("67".equals(moneyRole.getCode())) {
						calendar = Calendar.getInstance();
						calendar.set(Calendar.HOUR_OF_DAY, 0);
						calendar.set(Calendar.MINUTE, 0);
						calendar.set(Calendar.SECOND, 0);
						calendar.set(Calendar.MILLISECOND, 0);
						param2 = calendar.getTime();
						calendar.set(Calendar.HOUR_OF_DAY, 23);
						calendar.set(Calendar.MINUTE, 59);
						calendar.set(Calendar.SECOND, 59);
						calendar.set(Calendar.MILLISECOND, 999);
						param3 = calendar.getTime();
						Query q = getHibernateTemplate().getSessionFactory()
								.getCurrentSession().createQuery(sb.toString());
						q.setParameter("end", param3);
						q.setParameter("start", param2);
						Object ob = q.uniqueResult();
						Double val = ob == null ? 0d : (Double) ob;
						if (Double.compare(moneyRole.getCodevalue(), val) <= 0) {
							sendMail(amount, moneyRole.getCodename(),
									as.getAccountName());
							saveLog(as, amount, moneyRole.getCodename());
							sign = "67";
							isIinfract = true;
						}
					}
					if ("68".equals(moneyRole.getCode())) {
						calendar = Calendar.getInstance();
						calendar.set(Calendar.DAY_OF_MONTH, 1);
						calendar.set(Calendar.HOUR_OF_DAY, 0);
						calendar.set(Calendar.MINUTE, 0);
						calendar.set(Calendar.SECOND, 0);
						calendar.set(Calendar.MILLISECOND, 0);
						param2 = calendar.getTime();
						calendar.set(Calendar.DAY_OF_MONTH, calendar
								.getActualMaximum(Calendar.DAY_OF_MONTH));
						calendar.set(Calendar.HOUR_OF_DAY, 23);
						calendar.set(Calendar.MINUTE, 59);
						calendar.set(Calendar.SECOND, 59);
						calendar.set(Calendar.MILLISECOND, 999);
						param3 = calendar.getTime();
						Query q = getHibernateTemplate().getSessionFactory()
								.getCurrentSession().createQuery(sb.toString());
						q.setParameter("end", param3);
						q.setParameter("start", param2);
						Object ob = q.uniqueResult();
						Double val = ob == null ? 0d : (Double) ob;
						if (Double.compare(moneyRole.getCodevalue(), val) <= 0) {
							sendMail(amount, moneyRole.getCodename(),
									as.getAccountName());
							saveLog(as, amount, moneyRole.getCodename());
							sign = "68";
							isIinfract = true;
						}
					}
					if (isIinfract) {
						break;
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sign;
	}

	public String confirmToRuleByDrawToSelf(String amount) throws Exception {
		String sign = "";
		StringBuffer sb = new StringBuffer(
				"from MoneyRole m where m.code in ('69','70','71') and m.status='Y'");
		List list = getHibernateTemplate().find(sb.toString());
		// Account account = this.getAccountService().findAccountByEmail(
		// email);
		WebContext webContext = WebContextFactory.get();
		AccountSummary as = WeatherUtil.getLoingInfoFromSession(webContext
				.getHttpServletRequest());
		boolean isIinfract = false;
		sb.setLength(0);
		sb.append("select sum(t.amount) from Transaction t where t.actionType=4 and t.debit.id="
				+ as.getDefaultSubAccountId() + " and t.transStatus=3");
		Calendar calendar = null;
		Date param2 = null;
		Date param3 = null;
		sb.append(" and t.createTime <= :end and t.createTime>= :start");
		if (list.size() > 0) {
			for (Object object : list) {
				MoneyRole moneyRole = (MoneyRole) object;
				if ("69".equals(moneyRole.getCode())
						&& Double.compare(moneyRole.getCodevalue(),
								Double.valueOf(amount)) <= 0) {
					sendMail(amount, moneyRole.getCodename(),
							as.getAccountName());
					saveLog(as, amount, moneyRole.getCodename());
					sign = "69";
					isIinfract = true;
				}
				if ("70".equals(moneyRole.getCode())) {
					calendar = Calendar.getInstance();
					calendar.set(Calendar.HOUR_OF_DAY, 0);
					calendar.set(Calendar.MINUTE, 0);
					calendar.set(Calendar.SECOND, 0);
					calendar.set(Calendar.MILLISECOND, 0);
					param2 = calendar.getTime();
					calendar.set(Calendar.HOUR_OF_DAY, 23);
					calendar.set(Calendar.MINUTE, 59);
					calendar.set(Calendar.SECOND, 59);
					calendar.set(Calendar.MILLISECOND, 999);
					param3 = calendar.getTime();
					Query q = getHibernateTemplate().getSessionFactory()
							.getCurrentSession().createQuery(sb.toString());
					q.setParameter("end", param3);
					q.setParameter("start", param2);
					Object ob = q.uniqueResult();
					Double val = ob == null ? 0d : (Double) ob;
					if (Double.compare(moneyRole.getCodevalue(), val) <= 0) {
						sendMail(amount, moneyRole.getCodename(),
								as.getAccountName());
						saveLog(as, amount, moneyRole.getCodename());
						sign = "70";
						isIinfract = true;
					}
				}
				if ("71".equals(moneyRole.getCode())) {
					calendar = Calendar.getInstance();
					calendar.set(Calendar.DAY_OF_MONTH, 1);
					calendar.set(Calendar.HOUR_OF_DAY, 0);
					calendar.set(Calendar.MINUTE, 0);
					calendar.set(Calendar.SECOND, 0);
					calendar.set(Calendar.MILLISECOND, 0);
					param2 = calendar.getTime();
					calendar.set(Calendar.DAY_OF_MONTH,
							calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
					calendar.set(Calendar.HOUR_OF_DAY, 23);
					calendar.set(Calendar.MINUTE, 59);
					calendar.set(Calendar.SECOND, 59);
					calendar.set(Calendar.MILLISECOND, 999);
					param3 = calendar.getTime();
					Query q = getHibernateTemplate().getSessionFactory()
							.getCurrentSession().createQuery(sb.toString());
					q.setParameter("end", param3);
					q.setParameter("start", param2);
					Object ob = q.uniqueResult();
					Double val = ob == null ? 0d : (Double) ob;
					if (Double.compare(moneyRole.getCodevalue(), val) <= 0) {
						sendMail(amount, moneyRole.getCodename(),
								as.getAccountName());
						saveLog(as, amount, moneyRole.getCodename());
						sign = "71";
						isIinfract = true;
					}
				}
				if (isIinfract) {
					break;
				}
			}
		}
		return sign;
	}

	public String confirmToRuleByInneryPay(String amount) throws Exception {
		String sign = "";
		StringBuffer sb = new StringBuffer(
				"from MoneyRole m where m.code in ('72','73','74') and m.status='Y'");
		List list = getHibernateTemplate().find(sb.toString());
		// Account account = this.getAccountService().findAccountByEmail(
		// email);
		WebContext webContext = WebContextFactory.get();
		AccountSummary as = WeatherUtil.getLoingInfoFromSession(webContext
				.getHttpServletRequest());
		boolean isIinfract = false;
		sb.setLength(0);
		sb.append("select sum(t.amount) from Transaction t where t.actionType=3 and t.debit.id="
				+ as.getDefaultSubAccountId() + " and t.transStatus=3");
		Calendar calendar = null;
		Date param2 = null;
		Date param3 = null;
		sb.append(" and t.createTime <= :end and t.createTime>= :start");
		if (list.size() > 0) {
			for (Object object : list) {
				MoneyRole moneyRole = (MoneyRole) object;
				if ("72".equals(moneyRole.getCode())
						&& Double.compare(moneyRole.getCodevalue(),
								Double.valueOf(amount)) <= 0) {
					sendMail(amount, moneyRole.getCodename(),
							as.getAccountName());
					saveLog(as, amount, moneyRole.getCodename());
					sign = "72";
					isIinfract = true;
				}
				if ("73".equals(moneyRole.getCode())) {
					calendar = Calendar.getInstance();
					calendar.set(Calendar.HOUR_OF_DAY, 0);
					calendar.set(Calendar.MINUTE, 0);
					calendar.set(Calendar.SECOND, 0);
					calendar.set(Calendar.MILLISECOND, 0);
					param2 = calendar.getTime();
					calendar.set(Calendar.HOUR_OF_DAY, 23);
					calendar.set(Calendar.MINUTE, 59);
					calendar.set(Calendar.SECOND, 59);
					calendar.set(Calendar.MILLISECOND, 999);
					param3 = calendar.getTime();
					Query q = getHibernateTemplate().getSessionFactory()
							.getCurrentSession().createQuery(sb.toString());
					q.setParameter("end", param3);
					q.setParameter("start", param2);
					Object ob = q.uniqueResult();
					Double val = ob == null ? 0d : (Double) ob;
					if (Double.compare(moneyRole.getCodevalue(), val) <= 0) {
						sendMail(amount, moneyRole.getCodename(),
								as.getAccountName());
						saveLog(as, amount, moneyRole.getCodename());
						sign = "73";
						isIinfract = true;
					}
				}
				if ("74".equals(moneyRole.getCode())) {
					calendar = Calendar.getInstance();
					calendar.set(Calendar.DAY_OF_MONTH, 1);
					calendar.set(Calendar.HOUR_OF_DAY, 0);
					calendar.set(Calendar.MINUTE, 0);
					calendar.set(Calendar.SECOND, 0);
					calendar.set(Calendar.MILLISECOND, 0);
					param2 = calendar.getTime();
					calendar.set(Calendar.DAY_OF_MONTH,
							calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
					calendar.set(Calendar.HOUR_OF_DAY, 23);
					calendar.set(Calendar.MINUTE, 59);
					calendar.set(Calendar.SECOND, 59);
					calendar.set(Calendar.MILLISECOND, 999);
					param3 = calendar.getTime();
					Query q = getHibernateTemplate().getSessionFactory()
							.getCurrentSession().createQuery(sb.toString());
					q.setParameter("end", param3);
					q.setParameter("start", param2);
					Object ob = q.uniqueResult();
					Double val = ob == null ? 0d : (Double) ob;
					if (Double.compare(moneyRole.getCodevalue(), val) <= 0) {
						sendMail(amount, moneyRole.getCodename(),
								as.getAccountName());
						saveLog(as, amount, moneyRole.getCodename());
						sign = "74";
						isIinfract = true;
					}
				}
				if (isIinfract) {
					break;
				}
			}
		}
		return sign;
	}
	
	public String confirmToRuleByTransfer(String amount,HttpServletRequest request)throws Exception {
		String sign="";
		StringBuffer sb=new StringBuffer("from MoneyRole m where m.code in ('10') and m.status='Y'");
		List list = getHibernateTemplate().find(sb.toString());
		AccountSummary as=WeatherUtil.getLoingInfoFromSession(request);
		if (list.size() > 0) {
			for (Object object : list) {
				MoneyRole moneyRole=(MoneyRole)object;
				if ("10".equals(moneyRole.getCode())&&Double
						.compare(moneyRole.getCodevalue(), Double.valueOf(amount)) <= 0) {
					sendMail(amount, moneyRole.getCodename(),as.getAccountName());
					saveLog(as,amount,moneyRole.getCodename());
					sign="10";
				}
			}
		}
		return sign;
	}

	public void noticeMoenyRole(MoneyRole moneyRole, String amount, Account acct)
			throws Exception {

		sendMail(amount, moneyRole.getCodename(), acct.getName());
		saveLog(acct, amount, moneyRole.getCodename());
	}

	public void sendMail(String amount, String codeName, String name) {
		// 发送预警邮件
		VNVMail mail = VNVMail.createRuleMail2(this.email, codeName, amount,
				name);
		this.notificationService.sendMail(mail);
	}

	public String authentication(String subAccountId) {
		String result = "false";
		SubAccount sb = this.getSubAccount(subAccountId);
		Account account = sb.getAccount();
		if (account.getAuthenticationStatus() == null) {
			result = "true";
		} else {
			AuthenticationStatus authenticationStatus = AuthenticationStatus
					.getInstance(account.getAuthenticationStatus());
			String status = authenticationStatus
					.getStatus(Constant.AUTHENTICATION_METHOD_IDCARD);
			if (!Constant.AUTHENTICATION_STATUS_PASS.equals(status)) {
				result = "true";
			}
		}

		return result;
	}

	private void saveLog(AccountSummary as, String amount, String remark) {
		SystemLog sysLog = new SystemLog();
		sysLog.setAccount(as.getAccount());
		sysLog.setAccountName(as.getAccountName());
		sysLog.setAccountId(as.getAccount().getAccountId());
		sysLog.setLoginIp("9999");// 区分是用户登录日志和违反反洗钱规则交易日志
		sysLog.setLoginTime(new Date());
		sysLog.setLoginResult(amount);
		sysLog.setRemark("违反" + remark);
		this.accountDAO.updateLog(sysLog);
	}

	public void saveLog(Account acct, String amount, String remark) {
		SystemLog sysLog = new SystemLog();
		sysLog.setAccount(acct);
		sysLog.setAccountName(acct.getName());
		sysLog.setAccountId(acct.getAccountId());
		sysLog.setLoginIp("9999");// 区分是用户登录日志和违反反洗钱规则交易日志
		sysLog.setLoginTime(new Date());
		sysLog.setLoginResult(amount);
		sysLog.setRemark("违反" + remark);
		this.accountDAO.updateLog(sysLog);
	}

	public void setAccountDAO(IAccountDAO accountDAO) {
		this.accountDAO = accountDAO;
	}

}